﻿------创建人：王涛       创建时间：2011年1月12日 -------
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='Proc_GetDeclarationInfo' AND TYPE='P')
BEGIN
  DROP PROCEDURE Proc_GetDeclarationInfo
END
GO
CREATE PROC [dbo].[Proc_GetDeclarationInfo]
 	@PageIndex int,
	@PageSize int,
	@EnterpriseName varchar(100),
	@Region varchar(20),
	@RegType varchar(20),
	@IndustryType varchar(50)
 AS
DECLARE @SQL nvarchar(1000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(200)
IF @PageIndex <1 SET @PageIndex = 1
SET @PageIndex = (@PageIndex-1) * @PageSize + 1
SET @Where = ''
IF ((@EnterpriseName IS NOT NULL) AND (RTRIM(LTRIM(@EnterpriseName))<>''))
BEGIN
  SET @Where = @Where +  ' AND EnterpriseName LIKE ''%'+ @EnterpriseName + '%'''
END
IF (@Region IS NOT NULL )  AND (RTRIM(LTRIM(@Region))<>'')
BEGIN
  SET @Where = @Where +  ' AND Region = '''+ @Region + ''''
END

IF ((@RegType IS NOT NULL) AND (RTRIM(LTRIM(@RegType))<>''))
BEGIN
  SET @Where = @Where +  ' AND RegType = '''+ @RegType + ''''
END

IF ((@IndustryType IS NOT NULL) AND (RTRIM(LTRIM(@IndustryType))<>''))
BEGIN
  SET @Where = @Where +  ' AND IndustryType LIKE ''%'+ @IndustryType + '%'''
END

IF ((LEN(@Where)>0) AND (LEFT(@Where,5)=' AND')) 
BEGIN
  SET @Where = Right(@Where,LEN(@Where)-5)
END
SET @SQL = N'SELECT * FROM  DeclarationInfo C JOIN Regions R ON R.RegionCode=C.Region Left JOIN Registrations E ON E.RegistrationCode=C.RegType ORDER BY EnterpriseID DESC'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'SELECT * FROM  DeclarationInfo C JOIN Regions R ON R.RegionCode=C.Region Left JOIN Registrations E ON E.RegistrationCode=C.RegType WHERE ' + @Where + ' ORDER BY EnterpriseID DESC'
END
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 16, @PageIndex, @PageSize 
  EXEC sp_cursorclose @P1
END
select @RecordCount
PRINT @SQL
return @@ERROR
 